SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE sprTheoDoiChuongTrinhChietKhauMotQuyReport
	@BranchID UNIQUEIDENTIFIER,
	@Month INT,
	@Year INT,
	@BrandID INT
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @BrandCode NVARCHAR(20)
	SELECT @BrandCode = BrandCode FROM dbo.ThuongHieu WHERE BrandID = @BrandID
	
	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)
	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID
	
	DECLARE @Duration int
	SET @Duration = 3

	SELECT * FROM (
	SELECT mr.KhachHangID
	, Thang = 'TD' + CAST(DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME)) as nvarchar(2))
	, DoanhSoTD as DoanhSo
	, MucChietKhauTD
	, MucChietKhauCC = 0
	, th.BrandName
	, @TrungTamName as TrungTamName
	, ds.DoanhSo as DoanhSoCamKet
	, ds.DoanhSoLapQuy
	, [AccountingObjectCode]
		,[AccountingObjectName]
	FROM vwGetTotalMonthlyRevenue mr
	LEFT JOIN [ATAHANOI].[dbo].[AccountingObject] ao ON mr.KhachHangID = ao.AccountingObjectID
	inner join KhachHangDuThuong kh ON mr.KhachHangID = kh.HoiVienID
	AND Duration=@Duration
	INNER JOIN ThuongHieu th ON th.BrandID = kh.BrandID
	INNER JOIN DoanhSoCamKet ds ON ds.KhachHangDuThuongID = kh.HoiVienID AND ds.[Month] = DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME))
	WHERE CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	AND CAST((cast(@Year as nvarchar(4)) + '-' + CAST(@Month as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	AND @BrandID = kh.BrandID
	AND CHARINDEX('TD', LoaiSanPham) > 0
	UNION
	SELECT mr.KhachHangID
	, Thang = 'CC' + CAST(DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME)) as nvarchar(2))
	, DoanhSoCC as DoanhSo
	, MucChietKhauTD = 0
	, MucChietKhauCC
	, th.BrandName
	, @TrungTamName as TrungTamName
	, ds.DoanhSo as DoanhSoCamKet
	, ds.DoanhSoLapQuy
	, [AccountingObjectCode]
		,[AccountingObjectName]
	FROM vwGetTotalMonthlyRevenue mr
	LEFT JOIN [ATAHANOI].[dbo].[AccountingObject] ao ON mr.KhachHangID = ao.AccountingObjectID
	inner join KhachHangDuThuong kh ON mr.KhachHangID = kh.HoiVienID AND Duration=@Duration
	INNER JOIN ThuongHieu th ON th.BrandID = kh.BrandID
	INNER JOIN DoanhSoCamKet ds ON ds.KhachHangDuThuongID = kh.HoiVienID AND ds.[Month] = DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME))

	WHERE CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	AND CAST((cast(@Year as nvarchar(4)) + '-' + CAST(@Month as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
	AND @BrandID = kh.BrandID
	AND CHARINDEX('CC', LoaiSanPham) > 0
	) AS TDTable
	pivot (SUM (DoanhSo) FOR Thang IN ([TD0], [TD1], [TD2], [CC0], [CC1], [CC2])) as pvt 
	--exec sprTheoDoiChuongTrinhChietKhauMotQuyReport '5CF2DF83-3799-43FE-AAF5-04968C57F029', 1, 2010, 1
END
GO
